package com.hl.transportationhelper;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import android.content.Context;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper{
	
	
	private  static String test ="ko co du lieu";
	public static final String TAG = "Log>> ";
	private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "dbTauXe";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		
	}

	public void createTableGioTau(String tbName,String link, AssetManager am) throws IOException {
		
		String queryCreateTable = "";	
		String queryInsertTable = "";
	    InputStream fs;
	    ByteArrayOutputStream outStream = null;
	    SQLiteDatabase db = null;
		
		try {
			
			fs = am.open(link);
       	 //create a buffer that has the same size as the InputStream  
           byte[] buffer = new byte[fs.available()];  
           //read the text file as a stream, into the buffer  
           fs.read(buffer);  
           //create a output stream to write the buffer into  
            outStream = new ByteArrayOutputStream();  
           //write this buffer to the output stream  
           outStream.write(buffer);  
           //Close the Input and Output streams  
           outStream.close();  
           fs.close();  
			
			Document doc = Jsoup.parse(outStream.toString());
			
			Element tblContent = doc.getElementById("grv");
			Elements tblRows = tblContent.getElementsByTag("tr");
			
			int countColumn = Jsoup.parse("<table>" + tblRows.first().toString() +"</table>").select("th").size();
						
			db = this.getWritableDatabase();
			db.execSQL("DROP TABLE IF EXISTS "+ tbName);
			queryCreateTable = "CREATE TABLE IF NOT EXISTS "+ tbName +"( TenGa TEXT,";
			
			boolean checkFirstElem = false;
			for(Element eachRow : tblRows){
				
				checkFirstElem = true;
				for(int i=1;i<countColumn; i++){
					
					if(i == countColumn-1){
						queryCreateTable += eachRow.child(i).text() +" TEXT" +")";
						break;
					}
					else
						queryCreateTable += eachRow.child(i).text() +" TEXT" +",";
				}
												
				if(checkFirstElem)
					break;
			}					
			
			test = queryCreateTable;
	        db.execSQL(queryCreateTable);
	        
	      //Insert	       
	      	byte count = 0;
			for(Element eachRow : tblRows){
				
				count++;
				if(count == 1)
					continue;
				
				queryInsertTable = "INSERT INTO "+tbName+" VALUES("	; 
				for(int i=0;i<countColumn; i++){
					
					if(i == countColumn-1){
						queryInsertTable += "'"+ eachRow.child(i).text() +"'" +")";
						break;
					}
					else
						queryInsertTable += "'"+ eachRow.child(i).text() +"'" +",";
				}
												
				db.execSQL(queryInsertTable);
			}		
	                
			
		} catch (SQLException se) {
			
			Log.e(TAG, "Error" + se.getMessage());
			
		}finally{
			
			if (db != null)
				db.close();
			
		}  
		Log.i(TAG, queryCreateTable);
		test = queryInsertTable;
		
	}
	
	
	public String getString(){
		
		return test;
		
	}
	
	public ArrayList<String> getGaDi(String tbName, ArrayList<String> arrlGaDi) {
		
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			
			db = this.getReadableDatabase();
			String selectQuery = "SELECT TenGa FROM " + tbName + ";";			 
		     
		    cursor = db.rawQuery(selectQuery, null);
		 
		    // looping through all rows and adding to list
		    if (cursor.moveToFirst()) {
		        do {
		        	
		           arrlGaDi.add(cursor.getString(0));
		           
		        } while (cursor.moveToNext());
		    }
		    
						
		} catch (SQLException se) {
			se.printStackTrace();
		}finally{
			
			if (db != null)
				db.close();	
			if (cursor != null)
				db.close();	
		}
		
		return arrlGaDi;
	}
	
	public ArrayList<String> getMaTau(String tbName, ArrayList<String> arrlMaTau){
		
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			
			db = this.getReadableDatabase();
			String selectQuery = "PRAGMA table_info("+ tbName +")" + ";";			 
		     
		    cursor = db.rawQuery(selectQuery, null);
		 
		    // looping through all rows and adding to list
		    if (cursor.moveToFirst()) {
		        do {
		        	
		           arrlMaTau.add(cursor.getString(1));
		           
		        } while (cursor.moveToNext());
		    }
		    
						
		} catch (SQLException se) {
			se.printStackTrace();
		}finally{
			
			if (cursor != null)				
				cursor.close();
			if (db != null)
				db.close();	
			
		}
		
		return arrlMaTau;		
		
	} 
	
public List<String> getGioTau(String tbName, String maTau, int limit, int offset, List<String> arrlGioTau){
		
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			
			db = this.getReadableDatabase();
			String selectQuery = "SELECT TenGa, "+ maTau +" FROM " + tbName +" LIMIT "+ limit +" OFFSET "+offset;			 
		    
			test = selectQuery;
			
		    cursor = db.rawQuery(selectQuery, null);
		    
		    // looping through all rows and adding to list
		    if ( cursor != null) {
		    	
		    	cursor.moveToFirst();
		        do {
		        	
		        	arrlGioTau.add(cursor.getString(0));
		        	arrlGioTau.add(cursor.getString(1));		        	
		           
		        } while (cursor.moveToNext());
		    }
		    
		   
		        	
						
		} catch (SQLException se) {
			se.printStackTrace();
		}finally{
			
			if (cursor != null)
				cursor.close();
			if (db != null)
				db.close();	
				
		}
		
		return arrlGioTau;		
		
	} 

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
	}
	
	

}
